<!--  
		Author : Kông Kiều
		Date Created : June, 24th 2014
-->    
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class model_thongke extends CI_Model
{
    public function __construct() {
        parent::__construct();
        
    }

    // Dung de thong ke tinh hinh hoc sinh trong trường
    // về học tập (tỉ lệ giỏi, khá, trung bình,bỏ học)
    
    public function hocluc($nam,$hocky)
    {
    	$this->db->select("count(mahocsinh) as mhs,hocluc");
        $this->db->from("xeploai");
        $this->db->where("namhoc",$nam);
        $this->db->where("hocky",$hocky);
        $this->db->group_by('hocluc');
        $query = $this->db->get();
         return $query->result();
    }
    
    public function hanhkiem($nam,$hocky)
    {
        $this->db->select("count(mahocsinh) as mhs,hanhkiem");
        $this->db->from("xeploai");
        $this->db->where("namhoc",$nam);
        $this->db->where("hocky",$hocky);
        $this->db->group_by('hanhkiem');
        $query = $this->db->get();
         return $query->result();
    }
   
    public function hocluckhoi($nam,$hocky,$khoi)
    {
        $this->db->select("count(mahocsinh) as mhs,hocluc",FALSE);
        $this->db->from("xeploai");
        $this->db->where("namhoc",$nam);
        $this->db->where("hocky",$hocky);
        $this->db->where("substring(malop,5,1)",$khoi);
        $this->db->group_by("hocluc");
        $query = $this->db->get();
         return $query->result();
    }
    public function hanhkiemkhoi($nam,$hocky,$khoi)
    {
        $this->db->select("count(mahocsinh) as mhs,hanhkiem",FALSE);
        $this->db->from("xeploai");
        $this->db->where("namhoc",$nam);
        $this->db->where("hocky",$hocky);
        $this->db->where("substring(malop,5,1)",$khoi);
        $this->db->group_by("hanhkiem");
        $query = $this->db->get();
         return $query->result();
    }
    public function nghihoc()
    {
        $this->db->select("count(hocsinh.mahocsinh) as mhs");
        $this->db->from("hocsinh");
        $this->db->join("xeploai",'xeploai.mahocsinh = hocsinh.mahocsinh');
        $this->db->where("namhoc <=",'YEAR(now())');
         $this->db->or_where("namhoc >",'YEAR(now())-5');
        $this->db->group_by(array("tinhtrang","hocky"));
        $this->db->having("tinhtrang",2);
        $query = $this->db->get();
         return $query->result();
    }

    
    

}